﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using iHub.App_Code;
using System.Text.RegularExpressions;

namespace iHub
{
    class AssignmentManager : dbConnector
    {
        private SqlDataReader read;
        private SqlCommand command;
        private SqlParameter param;

        public void InsertAssign(Assignment newAssignment)
        {
            int randnum = 0;
            string accountnames = "";
            string append = "/";

            try
            {
                SecureRandom secure = new SecureRandom();
                randnum = secure.GenerateNumber();

                if (newAssignment.Accountnames.Count > 0)
                {
                    accountnames = newAssignment.Accountnames[0].ToString();

                    if (newAssignment.Accountnames.Count > 1)
                    {
                        for (int i = 1; newAssignment.Accountnames.Count > i; i++ )
                        {
                            accountnames += append + newAssignment.Accountnames[i].ToString();
                        }
                    }
                }

                connect();

                if (newAssignment.Eid != "")
                {
                    command = new SqlCommand("dbo.AddAssignment", getCon());
                    command.CommandType = CommandType.StoredProcedure;
                }
                else
                {
                    command = new SqlCommand("dbo.AddAssignmentNoEmployee", getCon());
                    command.CommandType = CommandType.StoredProcedure;
                }
                //insert paramaters / values
                param = new SqlParameter("@assign_id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = randnum;
                command.Parameters.Add(param);
                if (newAssignment.Eid != "")
                {
                    param = new SqlParameter("@e_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(newAssignment.Eid);
                    command.Parameters.Add(param);
                }
                param = new SqlParameter("@request", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Request;
                command.Parameters.Add(param);
                param = new SqlParameter("@assign", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Assign;
                command.Parameters.Add(param);
                param = new SqlParameter("@batch", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToInt32(newAssignment.Batch);
                command.Parameters.Add(param);
                param = new SqlParameter("@status", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Status;
                command.Parameters.Add(param);
                param = new SqlParameter("@startdate", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Startdate;
                command.Parameters.Add(param);
                param = new SqlParameter("@enddate", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Enddate;
                command.Parameters.Add(param);
                param = new SqlParameter("@address", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Address;
                command.Parameters.Add(param);
                param = new SqlParameter("@city", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.City;
                command.Parameters.Add(param);
                if (newAssignment.Eid != "")
                {
                    param = new SqlParameter("@ename", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Ename;
                    command.Parameters.Add(param);
                }
                param = new SqlParameter("@type", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToInt32(newAssignment.Type);
                command.Parameters.Add(param);
                param = new SqlParameter("@subjecttype", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Subjecttype;
                command.Parameters.Add(param);
                param = new SqlParameter("@requestor", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Requestor;
                command.Parameters.Add(param);
                param = new SqlParameter("@ci", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Ci;
                command.Parameters.Add(param);
                param = new SqlParameter("@accountname", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = accountnames;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                command.Parameters.Clear();

                disconnect();
            }
            catch (Exception e)
            {
                disconnect();
                throw new Exception(e.ToString());
            }
        }

        public void UpdateAssign(Assignment newAssignment)
        {
            try
            {
                connect();

                command = new SqlCommand("dbo.EditAssignEmployee", getCon());
                command.CommandType = CommandType.StoredProcedure;

                //insert paramaters / values

                param = new SqlParameter("@e_id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToInt32(newAssignment.Eid);
                command.Parameters.Add(param);
                param = new SqlParameter("@e_name", SqlDbType.VarChar);
                param.Direction = ParameterDirection.Input;
                param.Value = newAssignment.Ename;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public DataTable ViewAssign()
        {
            DataTable assignments = new DataTable();
            int i = 1;

            assignments.Columns.Add(new DataColumn("Assignment ID", typeof(int)));
            assignments.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
            assignments.Columns.Add(new DataColumn("Batch No.", typeof(int)));
            assignments.Columns.Add(new DataColumn("IHUB C.I.", typeof(string)));
            assignments.Columns.Add(new DataColumn("Employee ID", typeof(int)));
            assignments.Columns.Add(new DataColumn("RCBC C.I.", typeof(string)));
            assignments.Columns.Add(new DataColumn("Account Name(s)", typeof(string)));
            assignments.Columns.Add(new DataColumn("Subject Type", typeof(string)));
            assignments.Columns.Add(new DataColumn("Address", typeof(string)));
            assignments.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
            assignments.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
            assignments.Columns.Add(new DataColumn("Requestor", typeof(string)));
            assignments.Columns.Add(new DataColumn("Description", typeof(string)));
            assignments.Columns.Add(new DataColumn("Amount", typeof(int)));
            assignments.Columns.Add(new DataColumn("Status", typeof(string)));
            assignments.Columns.Add(new DataColumn("Date of Endorsement", typeof(string)));
            assignments.Columns.Add(new DataColumn("Date of Submission", typeof(string)));

            //   string conn = "Data Source=GABS-PC\\SQLEXPRESS;Initial Catalog=DB_IHUB;Integrated Security=True";
            try
            {
                connect();

                command = new SqlCommand("select Assignment.*, Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type order by batchno,city", getCon());
                read = command.ExecuteReader();

                while (read.Read())
                {
                    DataRow newRow1 = assignments.NewRow();

                    newRow1["Assignment ID"] = read.GetValue(0);
                    newRow1["Assignment No."] = i;
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["IHUB C.I."] = read.GetValue(2);
                    newRow1["Account Name(s)"] = read.GetValue(3);
                    newRow1["Subject Type"] = read.GetValue(4);
                    newRow1["RCBC C.I."] = read.GetValue(5);
                    newRow1["Requestor"] = read.GetValue(6);
                    newRow1["Requesting Unit"] = read.GetValue(7);
                    newRow1["Description"] = read.GetValue(8);
                    newRow1["Batch No."] = read.GetValue(9);
                    newRow1["Status"] = read.GetValue(10);
                    newRow1["Date of Endorsement"] = read.GetValue(11);
                    newRow1["Date of Submission"] = read.GetValue(12);
                    newRow1["Address"] = read.GetValue(13);
                    newRow1["City/Municipality"] = read.GetValue(14);
                    newRow1["Amount"] = read.GetValue(16);
                    assignments.Rows.Add(newRow1);
                    i++;
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return assignments;
        }

        public DataTable FilterAssign(String a, String b)
        {
            String parameter = a;
            String parameter2 = b;
            DataTable filter = new DataTable();
            int i = 1;

            try
            {
                connect();

                filter = new DataTable();
                filter.Columns.Add(new DataColumn("Assignment ID", typeof(int)));
                filter.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
                filter.Columns.Add(new DataColumn("Batch No.", typeof(int)));
                filter.Columns.Add(new DataColumn("IHUB C.I.", typeof(string)));
                filter.Columns.Add(new DataColumn("Employee ID", typeof(int)));
                filter.Columns.Add(new DataColumn("RCBC C.I.", typeof(string)));
                filter.Columns.Add(new DataColumn("Account Name(s)", typeof(string)));
                filter.Columns.Add(new DataColumn("Subject Type", typeof(string)));
                filter.Columns.Add(new DataColumn("Address", typeof(string)));
                filter.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
                filter.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
                filter.Columns.Add(new DataColumn("Requestor", typeof(string)));
                filter.Columns.Add(new DataColumn("Description", typeof(string)));
                filter.Columns.Add(new DataColumn("Amount", typeof(int)));
                filter.Columns.Add(new DataColumn("Status", typeof(string)));
                filter.Columns.Add(new DataColumn("Date of Endorsement", typeof(string)));
                filter.Columns.Add(new DataColumn("Date of Submission", typeof(string)));

                command = new SqlCommand("select Assignment.*, Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type Where "+parameter2+" LIKE '%"+parameter+"%' order by batchno,city", getCon());

                read = command.ExecuteReader();
                filter.Rows.Clear();
                while (read.Read())
                {
                    DataRow newRow1 = filter.NewRow();

                    newRow1["Assignment ID"] = read.GetValue(0);
                    newRow1["Assignment No."] = i;
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["IHUB C.I."] = read.GetValue(2);
                    newRow1["Account Name(s)"] = read.GetValue(3);
                    newRow1["Subject Type"] = read.GetValue(4);
                    newRow1["RCBC C.I."] = read.GetValue(5);
                    newRow1["Requestor"] = read.GetValue(6);
                    newRow1["Requesting Unit"] = read.GetValue(7);
                    newRow1["Description"] = read.GetValue(8);
                    newRow1["Batch No."] = read.GetValue(9);
                    newRow1["Status"] = read.GetValue(10);
                    newRow1["Date of Endorsement"] = read.GetValue(11);
                    newRow1["Date of Submission"] = read.GetValue(12);
                    newRow1["Address"] = read.GetValue(13);
                    newRow1["City/Municipality"] = read.GetValue(14);
                    newRow1["Amount"] = read.GetValue(16);
                    filter.Rows.Add(newRow1);
                    i++;
                }
                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return filter;
        }

        public void EditAssign(Assignment newAssignment)
        {
            string accountnames = "";
            string append = "/";
            bool paid = false;

            try
            {
                connect();

                command = new SqlCommand("select * from Payroll_Assignment where assign_id = @assign_id",getCon());
                command.CommandType = CommandType.Text;
                param = new SqlParameter("@assign_id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = Convert.ToInt32(newAssignment.AssignId);
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                if (read.Read())
                {
                    paid = true;
                }
                else
                {
                    paid = false;
                }
                read.Close();
                command.Parameters.Clear();

                if (!paid || (!newAssignment.Status.Equals("New") && !newAssignment.Status.Equals("Pending")))
                {
                    if (newAssignment.Accountnames.Count > 1)
                    {
                        accountnames = newAssignment.Accountnames[0].ToString();
                        for (int i = 1; newAssignment.Accountnames.Count > i; i++)
                        {
                            accountnames += append + newAssignment.Accountnames[i].ToString();
                        }
                    }
                    else if (newAssignment.Accountnames.Count > 0)
                    {
                        accountnames = newAssignment.Accountnames[0].ToString();
                    }

                    if (newAssignment.Eid != "")
                    {
                        command = new SqlCommand("dbo.EditAssignment", getCon());
                        command.CommandType = CommandType.StoredProcedure;
                    }
                    else
                    {
                        command = new SqlCommand("dbo.EditAssignmentNoEmployee", getCon());
                        command.CommandType = CommandType.StoredProcedure;
                    }

                    //insert paramaters / values
                    param = new SqlParameter("@assign_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(newAssignment.AssignId);
                    command.Parameters.Add(param);
                    if (newAssignment.Eid != "")
                    {
                        param = new SqlParameter("@e_id", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = Convert.ToInt32(newAssignment.Eid);
                        command.Parameters.Add(param);
                    }
                    param = new SqlParameter("@request", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Request;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@description", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Assign;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@batchno", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(newAssignment.Batch);
                    command.Parameters.Add(param);
                    param = new SqlParameter("@status", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Status;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@startdate", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Startdate;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@enddate", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Enddate;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@address", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Address;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@city", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.City;
                    command.Parameters.Add(param);
                    if (newAssignment.Eid != "")
                    {
                        param = new SqlParameter("@e_name", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = newAssignment.Ename;
                        command.Parameters.Add(param);
                    }
                    param = new SqlParameter("@type", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(newAssignment.Type);
                    command.Parameters.Add(param);
                    param = new SqlParameter("@subjecttype", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Subjecttype;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@requestor", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Requestor;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@ci", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = newAssignment.Ci;
                    command.Parameters.Add(param);
                    param = new SqlParameter("@accountname", SqlDbType.VarChar);
                    param.Direction = ParameterDirection.Input;
                    param.Value = accountnames;
                    command.Parameters.Add(param);

                    command.ExecuteNonQuery();
                    command.Parameters.Clear();
                }
                else
                {
                    disconnect();
                    throw new Exception("Assignment cannot be changed to " + newAssignment.Status + ", it has already been paid for");
                }

                disconnect();
            }
            catch (Exception e)
            {
                disconnect();
                throw new Exception(e.ToString());
            }
        }

        public List<string> EmployeeAssign()
        {
            List<string> _items = new List<string>();
            try
            {
                connect();
                command = new SqlCommand("select * from Employee Where e_status = 1 ORDER by e_name ASC", getCon());
                read = command.ExecuteReader();
                String name1, dash = "-";
                String item;
                int id1;

                while (read.Read())
                {
                    name1 = (string)read.GetValue(2);
                    name1 = name1 += dash;
                    id1 = (int)read.GetValue(0);
                    item = name1 += id1;

                    _items.Add(item);
                }

                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return _items;
        }

        public DataTable ViewTable()
        {
            DataTable filter = new DataTable();
            DataRow newRow1;
            List<int> assign_ids = new List<int>();
            int i = 1;

            try
            {
                connect();

                filter = new DataTable();
                filter.Columns.Add(new DataColumn("Assignment ID", typeof(int)));
                filter.Columns.Add(new DataColumn("Assignment No.", typeof(int)));
                filter.Columns.Add(new DataColumn("Batch No.", typeof(int)));
                filter.Columns.Add(new DataColumn("IHUB C.I.", typeof(string)));
                filter.Columns.Add(new DataColumn("Employee ID", typeof(int)));
                filter.Columns.Add(new DataColumn("RCBC C.I.", typeof(string)));
                filter.Columns.Add(new DataColumn("Account Name(s)", typeof(string)));
                filter.Columns.Add(new DataColumn("Subject Type", typeof(string)));
                filter.Columns.Add(new DataColumn("Address", typeof(string)));
                filter.Columns.Add(new DataColumn("City/Municipality", typeof(string)));
                filter.Columns.Add(new DataColumn("Requesting Unit", typeof(string)));
                filter.Columns.Add(new DataColumn("Requestor", typeof(string)));
                filter.Columns.Add(new DataColumn("Description", typeof(string)));
                filter.Columns.Add(new DataColumn("Amount", typeof(int)));
                filter.Columns.Add(new DataColumn("Status", typeof(string)));
                filter.Columns.Add(new DataColumn("Date of Endorsement", typeof(string)));
                filter.Columns.Add(new DataColumn("Date of Submission", typeof(string)));

                command = new SqlCommand("select Assignment.*, Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type order by batchno,city", getCon());
                read = command.ExecuteReader();
                // read.Read();
                filter.Rows.Clear();
                while (read.Read())
                {
                    newRow1 = filter.NewRow();

                    newRow1["Assignment ID"] = read.GetValue(0);
                    newRow1["Assignment No."] = i;
                    newRow1["Employee Id"] = read.GetValue(1);
                    newRow1["IHUB C.I."] = read.GetValue(2);
                    newRow1["Account Name(s)"] = read.GetValue(3);
                    newRow1["Subject Type"] = read.GetValue(4);
                    newRow1["RCBC C.I."] = read.GetValue(5);
                    newRow1["Requestor"] = read.GetValue(6);
                    newRow1["Requesting Unit"] = read.GetValue(7);
                    newRow1["Description"] = read.GetValue(8);
                    newRow1["Batch No."] = read.GetValue(9);
                    newRow1["Status"] = read.GetValue(10);
                    newRow1["Date of Endorsement"] = read.GetValue(11);
                    newRow1["Date of Submission"] = read.GetValue(12);
                    newRow1["Address"] = read.GetValue(13);
                    newRow1["City/Municipality"] = read.GetValue(14);
                    newRow1["Amount"] = read.GetValue(16);
                    filter.Rows.Add(newRow1);
                    i++;
                }
                read.Close();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return filter;
        }

        public bool rateCheck(int rate)
        {
            bool check = true;
            int payment = rate;

            command.CommandText = "select payment from Assignment_Type where payment = @rate";
            command.CommandType = CommandType.Text;
            param = new SqlParameter("@rate", SqlDbType.Int);
            param.Direction = ParameterDirection.Input;
            param.Value = payment;
            command.Parameters.Add(param);
            read = command.ExecuteReader();
            if (read.Read())
            {
                check = false;
            }

            command.Parameters.Clear();
            read.Close();
            return check;
        }

        public bool typeCheck(int rate)
        {
            bool check = true;
            int payment = rate;

            try
            {
                connect();

                command = new SqlCommand("select payment from Assignment_Type where payment = @rate", getCon());
                param = new SqlParameter("@rate", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = payment;
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                if (read.Read())
                {
                    check = false;
                }

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }

            return check;
        }

        public void addRate(int rate)
        {
            int payment = rate;

            try
            {
                connect();

                command = new SqlCommand("INSERT INTO Assignment_Type (payment) VALUES (@pay)", getCon());
                param = new SqlParameter("@pay", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = payment;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public List<string> getRates()
        {
            List<string> rates = new List<string>();
            string i = "";
            string i2 = "";
            string rate = "";
            string dash = "-";

            try
            {
                connect();

                command = new SqlCommand("select * from Assignment_Type", getCon());
                read = command.ExecuteReader();
                while (read.Read())
                {
                    i = Convert.ToString(read["type"]);
                    i2 = Convert.ToString(read["payment"]);
                    rate = i + dash + i2;
                    rates.Add(rate);
                }
            }
            catch (Exception e)
            {
                disconnect();
                throw new Exception(e.Message);
            }

            disconnect();

            return rates;
        }

        public void modRate(int rate, int type)
        {
            int payment = rate;
            int t = type;

            try
            {
                connect();

                command = new SqlCommand("update Assignment_Type set payment = @pay where type = @type", getCon());
                param = new SqlParameter("@pay", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = payment;
                command.Parameters.Add(param);
                param = new SqlParameter("@type", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = t;
                command.Parameters.Add(param);

                command.ExecuteNonQuery();

                disconnect();
            }
            catch (Exception err)
            {
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public Assignment getAssignDetails(int i)
        {
            Assignment tempassign = new Assignment();
            List<string> names = new List<string>();
            string[] splittedstring;
            string tempname = "";

            try
            {
                connect();
                command = new SqlCommand("select * from Assignment where assign_id = @assignid", getCon());
                param = new SqlParameter("@assignid", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = i;
                command.Parameters.Add(param);
                read = command.ExecuteReader();

                if (read.Read())
                {
                    tempassign.Address = Convert.ToString(read.GetValue(13));
                    tempassign.Assign = Convert.ToString(read.GetValue(8));
                    tempassign.Batch = Convert.ToString(read.GetValue(9));
                    tempassign.Ci = Convert.ToString(read.GetValue(5));
                    tempassign.City = Convert.ToString(read.GetValue(14));
                    tempassign.Eid = Convert.ToString(read.GetValue(1));
                    tempassign.Ename = Convert.ToString(read.GetValue(2));
                    tempassign.Enddate = Convert.ToString(read.GetValue(12));
                    tempassign.Request = Convert.ToString(read.GetValue(7));
                    tempassign.Startdate = Convert.ToString(read.GetValue(11));
                    tempassign.Status = Convert.ToString(read.GetValue(10));
                    tempassign.Requestor = Convert.ToString(read.GetValue(6));
                    tempassign.Subjecttype = Convert.ToString(read.GetValue(4));
                    tempassign.Type = Convert.ToString(read.GetValue(15));
                    tempname = Convert.ToString(read.GetValue(3));
                }

                splittedstring = tempname.Split('/');

                foreach (string name in splittedstring)
                {
                    names.Add(name);
                }

                tempassign.Accountnames = names;

                read.Close();
                disconnect();
            }
            catch (Exception e)
            {
                disconnect();
                throw new Exception(e.ToString());
            }

            return tempassign;
        }

        public Boolean DeleteAssignment(int id)
        {
            Boolean success = false;

            try
            {
                connect();

                command = new SqlCommand("delete from Assignment where assign_id = @assign_id and status != 'Complete'", getCon());
                param = new SqlParameter("@assign_id", SqlDbType.Int);
                param.Direction = ParameterDirection.Input;
                param.Value = id;
                command.Parameters.Add(param);
                if (command.ExecuteNonQuery() >= 1)
                {
                    success = true;
                }

                disconnect();
            }
            catch (Exception e)
            {
                disconnect();
                throw new Exception(e.ToString());
            }
            return success;
        }

        public DataTable gettabulardata(string id, string no)
        {
            DataTable dt = new DataTable();
            int count = 1;
            string e_id = id;
            string batchno = no;

            try
            {
                connect();

                dt.Columns.Add(new DataColumn("Assign No.", typeof(int)));
                dt.Columns.Add(new DataColumn("RCBC C.I.", typeof(string)));
                dt.Columns.Add(new DataColumn("Account Name(s)", typeof(string)));
                dt.Columns.Add(new DataColumn("Address", typeof(string)));
                dt.Columns.Add(new DataColumn("Municipality", typeof(string)));
                dt.Columns.Add(new DataColumn("Requestor", typeof(string)));
                dt.Columns.Add(new DataColumn("Person", typeof(string)));
                dt.Columns.Add(new DataColumn("Date of Endorsement", typeof(string)));
                dt.Columns.Add(new DataColumn("Date of Submission", typeof(string)));
                dt.Columns.Add(new DataColumn("Name", typeof(string)));
                dt.Columns.Add(new DataColumn("Amount", typeof(float)));

                if (e_id != "" && batchno != "")
                {
                    command = new SqlCommand("select Assignment.ci,Assignment.accountname,Assignment.address,Assignment.city,Assignment.requestingunit,Assignment.requestor,Assignment.startdate,Assignment.enddate,Assignment.e_name,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type where Assignment.batchno = @batchno and Assignment.e_id = @e_id order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@batchno", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(batchno);
                    command.Parameters.Add(param);
                    param = new SqlParameter("@e_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(e_id);
                    command.Parameters.Add(param);
                }
                else if (e_id != "" && batchno == "")
                {
                    command = new SqlCommand("select Assignment.ci,Assignment.accountname,Assignment.address,Assignment.city,Assignment.requestingunit,Assignment.requestor,Assignment.startdate,Assignment.enddate,Assignment.e_name,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type where Assignment.e_id = @e_id order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@e_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(e_id);
                    command.Parameters.Add(param);
                }
                else if (e_id == "" && batchno != "")
                {
                    command = new SqlCommand("select Assignment.ci,Assignment.accountname,Assignment.address,Assignment.city,Assignment.requestingunit,Assignment.requestor,Assignment.startdate,Assignment.enddate,Assignment.e_name,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type where Assignment.batchno = @batchno order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@batchno", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(batchno);
                    command.Parameters.Add(param);
                }
                else if (e_id == "" && batchno == "")
                {
                    command = new SqlCommand("select Assignment.ci,Assignment.accountname,Assignment.address,Assignment.city,Assignment.requestingunit,Assignment.requestor,Assignment.startdate,Assignment.enddate,Assignment.e_name,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.type = Assignment_Type.type order by Assignment.requestingunit", getCon());
                }
                else
                {
                    disconnect();
                    throw new Exception("Cannot construct query with the following parameters");
                }
                read = command.ExecuteReader();

                while (read.Read())
                {
                    DataRow newRow = dt.NewRow();

                    newRow["Assign No."] = count;
                    newRow["RCBC C.I."] = Convert.ToString(read.GetValue(0));
                    newRow["Account Name(s)"] = Convert.ToString(read.GetValue(1));
                    newRow["Address"] = Convert.ToString(read.GetValue(2));
                    newRow["Municipality"] = Convert.ToString(read.GetValue(3));
                    newRow["Requestor"] = Convert.ToString(read.GetValue(4));
                    newRow["Person"] = Convert.ToString(read.GetValue(5));
                    newRow["Date of Endorsement"] = Convert.ToString(read.GetValue(6));
                    newRow["Date of Submission"] = Convert.ToString(read.GetValue(7));
                    newRow["Name"] = Convert.ToString(read.GetValue(8));
                    newRow["Amount"] = read.GetValue(9);
                    dt.Rows.Add(newRow);
                    count++;
                }

                read.Close();
                disconnect();
            }catch(Exception err){
                read.Close();
                disconnect();
                throw new Exception(err.ToString());
            }

            return dt;
        }

        public DataTable getrcbctable(string id, string no)
        {
            DataTable dt = new DataTable();
            int count = 1;
            string e_id = id;
            string batchno = no;

            try
            {
                connect();

                dt.Columns.Add(new DataColumn("Assign No.", typeof(int)));
                dt.Columns.Add(new DataColumn("Date of Endorsement", typeof(string)));
                dt.Columns.Add(new DataColumn("Account Name(s)", typeof(string)));
                dt.Columns.Add(new DataColumn("Municipality", typeof(string)));
                dt.Columns.Add(new DataColumn("Description", typeof(string)));
                dt.Columns.Add(new DataColumn("Amount", typeof(float)));
                dt.Columns.Add(new DataColumn("Requested By", typeof(string)));
                dt.Columns.Add(new DataColumn("Branch", typeof(string)));

                if (e_id != "" && batchno != "")
                {
                    command = new SqlCommand("select Assignment.startdate,Assignment.accountname,Assignment.city,Assignment.assignment,Assignment.requestor,Assignment.requestingunit,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.Type = Assignment_Type.Type where Assignment.batchno = @batchno and Assignment.e_id = @e_id order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@batchno", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(batchno);
                    command.Parameters.Add(param);
                    param = new SqlParameter("@e_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(e_id);
                    command.Parameters.Add(param);
                }
                else if (e_id != "" && batchno == "")
                {
                    command = new SqlCommand("select Assignment.startdate,Assignment.accountname,Assignment.city,Assignment.assignment,Assignment.requestor,Assignment.requestingunit,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.Type = Assignment_Type.Type where Assignment.e_id = @e_id order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@e_id", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(e_id);
                    command.Parameters.Add(param);
                }
                else if (e_id == "" && batchno != "")
                {
                    command = new SqlCommand("select Assignment.startdate,Assignment.accountname,Assignment.city,Assignment.assignment,Assignment.requestor,Assignment.requestingunit,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.Type = Assignment_Type.Type where Assignment.batchno = @batchno order by Assignment.requestingunit", getCon());
                    param = new SqlParameter("@batchno", SqlDbType.Int);
                    param.Direction = ParameterDirection.Input;
                    param.Value = Convert.ToInt32(batchno);
                    command.Parameters.Add(param);
                }
                else if (e_id == "" && batchno == "")
                {
                    command = new SqlCommand("select Assignment.startdate,Assignment.accountname,Assignment.city,Assignment.assignment,Assignment.requestor,Assignment.requestingunit,Assignment_Type.payment from Assignment left outer join Assignment_Type on Assignment.Type = Assignment_Type.Type order by Assignment.requestingunit", getCon());
                }
                else
                {
                    disconnect();
                    throw new Exception("Cannot construct query with the following parameters");
                }
                read = command.ExecuteReader();

                while (read.Read())
                {
                    DataRow newRow = dt.NewRow();

                    newRow["Assign No."] = count;
                    newRow["Date of Endorsement"] = read.GetValue(0);
                    newRow["Account Name(s)"] = read.GetValue(1);
                    newRow["Municipality"] = read.GetValue(2);
                    newRow["Description"] = read.GetValue(3);
                    newRow["Amount"] = read.GetValue(6);
                    newRow["Requested By"] = read.GetValue(4);
                    newRow["Branch"] = read.GetValue(5);
                    dt.Rows.Add(newRow);
                    count++;
                }
                printselect.formprint.toolStripProgressBar1.Value += 10;
                read.Close();
                disconnect();
            }
            catch (Exception err)
            {
                read.Close();
                disconnect();
                throw new Exception(err.ToString());
            }

            return dt;
        }

        public void importcontents(DataTable dt, int id, string name, string b)
        {
            string startdate;
            string accountname;
            string city;
            string requestor;
            string branch;
            string ci;
            string ename = name;
            int eid = id;
            string address;
            string enddate;
            int batch = 0;
            SecureRandom rand = new SecureRandom();
            int randnum = 0;
            int amount2 = 0;
            int temp;
            List<int> affected = new List<int>();
            int count = 0;
            string[] names;
            string tempamount;
            int tempamount2;
            string commandstring = "";

            if (!b.Equals(""))
            {
                batch = Convert.ToInt32(b);
            }
            else
            {
                batch = 0;
            }

            if (id != 0)
            {
                commandstring = "insert into Assignment (assign_id,ci,e_id,e_name,accountname,address,city,requestingunit,requestor,batchno,startdate,enddate) values(@assignid,@ci,@eid,@ename,@accname,@address,@city,@unit,@requestor,@batchno,@start,@end)";
            }
            else
            {
                commandstring = "insert into Assignment (assign_id,ci,accountname,address,city,requestingunit,requestor,batchno,startdate,enddate) values(@assignid,@ci,@accname,@address,@city,@unit,@requestor,@batchno,@start,@end)";
            }

            try
            {
                connect();
                beginTransaction();
                command = new SqlCommand();
                command.Connection = getCon();
                command.Transaction = getTransaction();

                foreach (DataRow row in dt.Rows)
                {
                    if (row[0].ToString().Contains("AMOUNT") && !row[0].ToString().Contains("TOTAL"))
                    {
                        tempamount = row[0].ToString();
                        tempamount = Regex.Replace(tempamount, "[^0-9]", "");
                        amount2 = Convert.ToInt32(tempamount);
                        tempamount2 = amount2 / count;

                        foreach (int num in affected)
                        {
                            if (rateCheck(tempamount2))
                            {
                                command.CommandText = "insert into Assignment_Type (payment) values(@pay)";
                                command.CommandType = CommandType.Text;
                                param = new SqlParameter("@pay", SqlDbType.Int);
                                param.Direction = ParameterDirection.Input;
                                param.Value = tempamount2;
                                command.Parameters.Add(param);
                                command.ExecuteNonQuery();
                                command.Parameters.Clear();

                                command.CommandText = "update Assignment set type = (select type from Assignment_Type where payment = @pay) where assign_id = @assign_id";
                                command.CommandType = CommandType.Text;
                                param = new SqlParameter("@pay", SqlDbType.Int);
                                param.Direction = ParameterDirection.Input;
                                param.Value = tempamount2;
                                command.Parameters.Add(param);
                                param = new SqlParameter("@assign_id", SqlDbType.Int);
                                param.Direction = ParameterDirection.Input;
                                param.Value = num;
                                command.Parameters.Add(param);
                                command.ExecuteNonQuery();
                                command.Parameters.Clear();
                            }
                            else
                            {
                                command.CommandText = "update Assignment set type = (select type from Assignment_Type where payment = @pay) where assign_id = @assign_id";
                                command.CommandType = CommandType.Text;
                                param = new SqlParameter("@pay", SqlDbType.Int);
                                param.Direction = ParameterDirection.Input;
                                param.Value = tempamount2;
                                command.Parameters.Add(param);
                                param = new SqlParameter("@assign_id", SqlDbType.Int);
                                param.Direction = ParameterDirection.Input;
                                param.Value = num;
                                command.Parameters.Add(param);
                                command.ExecuteNonQuery();
                                command.Parameters.Clear();
                            }
                        }

                        affected.Clear();
                        count = 0;
                    }
                    else if (int.TryParse(row[0].ToString(), out temp))
                    {
                        randnum = rand.GenerateNumber();
                        ci = row[1].ToString().Trim();
                        ci = Regex.Replace(ci, @"\s+", " ");
                        accountname = row[2].ToString().Trim();
                        accountname = Regex.Replace(accountname, @"\s+", " ");
                        address = row[3].ToString().Trim();
                        address = Regex.Replace(address, @"\s+", " ");
                        city = row[4].ToString().Trim();
                        city = Regex.Replace(city, @"\s+", " ");
                        names = row[5].ToString().Split('/');
                        branch = names[0].Trim();
                        branch = Regex.Replace(branch, @"\s+", " ");
                        requestor = "";
                        try { requestor = names[1].Trim(); requestor = Regex.Replace(requestor, @"\s+", " "); }
                        catch { requestor = ""; }
                        startdate = row[6].ToString();
                        enddate = row[7].ToString();

                        command.CommandText = commandstring;
                        command.CommandType = CommandType.Text;
                        param = new SqlParameter("@assignid", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = randnum;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@ci", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = ci;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@eid", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = eid;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@ename", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = ename;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@accname", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = accountname;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@address", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = address;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@city", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = city;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@unit", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = branch;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@requestor", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = requestor;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@batchno", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = batch;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@start", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = startdate;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@end", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = enddate;
                        command.Parameters.Add(param);
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();
                        count++;
                        affected.Add(randnum);
                    }
                }
                commit();
                disconnect();
            }
            catch (Exception err)
            {
                rollback();
                disconnect();
                throw new Exception(err.ToString());
            }
        }

        public void importcontents(DataTable dt, string b)
        {
            string startdate;
            string accountname;
            string city;
            string description;
            double amount;
            string requestor;
            string branch;
            int batch = 0; 
            SecureRandom rand = new SecureRandom();
            int randnum = 0;
            int amount2 = 0;
            int temp;

            if (!b.Equals(""))
            {
                batch = Convert.ToInt32(b);
            }
            else
            {
                batch = 0;
            }

            try
            {
                connect();
                beginTransaction();
                command = new SqlCommand();
                command.Connection = getCon();
                command.Transaction = getTransaction();

                foreach (DataRow row in dt.Rows)
                {
                    if (int.TryParse(row[0].ToString(), out temp))
                    {
                        randnum = rand.GenerateNumber();
                        startdate = row[1].ToString();
                        accountname = row[2].ToString().Trim();
                        accountname = Regex.Replace(accountname, @"\s+", " ");
                        city = row[3].ToString().Trim();
                        city = Regex.Replace(city, @"\s+", " ");
                        description = row[4].ToString().Trim();
                        description = Regex.Replace(description, @"\s+", " ");
                        amount = Convert.ToDouble(row[5].ToString());
                        requestor = row[7].ToString().Trim();
                        requestor = Regex.Replace(requestor, @"\s+", " ");
                        branch = row[8].ToString().Trim();
                        branch = Regex.Replace(branch, @"\s+", " ");
                        amount2 = Convert.ToInt32(amount);

                        command.CommandText = "insert into Assignment (assign_id,startdate,accountname,city,assignment,requestor,requestingunit,batchno) values (@assign_no,@startdate,@accountname,@city,@assignment,@requestor,@requestingunit,@batchno)";
                        command.CommandType = CommandType.Text;

                        param = new SqlParameter("@assign_no", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = randnum;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@startdate", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = startdate;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@accountname", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = accountname;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@city", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = city;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@assignment", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = description;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@requestor", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = requestor;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@requestingunit", SqlDbType.VarChar);
                        param.Direction = ParameterDirection.Input;
                        param.Value = branch;
                        command.Parameters.Add(param);
                        param = new SqlParameter("@batchno", SqlDbType.Int);
                        param.Direction = ParameterDirection.Input;
                        param.Value = batch;
                        command.Parameters.Add(param);
                        command.ExecuteNonQuery();
                        command.Parameters.Clear();

                        if (rateCheck(amount2))
                        {
                            command.CommandText = "insert into Assignment_Type (payment) values(@pay)";
                            command.CommandType = CommandType.Text;
                            param = new SqlParameter("@pay", SqlDbType.Int);
                            param.Direction = ParameterDirection.Input;
                            param.Value = amount2;
                            command.Parameters.Add(param);
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();

                            command.CommandText = "update Assignment set type = (select type from Assignment_Type where payment = @pay) where assign_id = @assign_id";
                            command.CommandType = CommandType.Text;
                            param = new SqlParameter("@pay", SqlDbType.Int);
                            param.Direction = ParameterDirection.Input;
                            param.Value = amount2;
                            command.Parameters.Add(param);
                            param = new SqlParameter("@assign_id", SqlDbType.Int);
                            param.Direction = ParameterDirection.Input;
                            param.Value = randnum;
                            command.Parameters.Add(param);
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                        else
                        {
                            command.CommandText = "update Assignment set type = (select type from Assignment_Type where payment = @pay) where assign_id = @assign_id";
                            command.CommandType = CommandType.Text;
                            param = new SqlParameter("@pay", SqlDbType.Int);
                            param.Direction = ParameterDirection.Input;
                            param.Value = amount2;
                            command.Parameters.Add(param);
                            param = new SqlParameter("@assign_id", SqlDbType.Int);
                            param.Direction = ParameterDirection.Input;
                            param.Value = randnum;
                            command.Parameters.Add(param);
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                        }
                    }
                }

                commit();
                disconnect();
            }
            catch (Exception err)
            {
                rollback();
                disconnect();
                throw new Exception(err.ToString());
            }
        }
    }
}